﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.Odbc;

namespace BSE.Windows.Test
{
    class AnesthesiaBLL
    {
        private static AnesthesiaDAL dal = new AnesthesiaDAL();

        public static List<Keshi> GetAllKeshi()
        {
            List<Keshi> ks = new List<Keshi>();
            DataSet ds = dal.GetDataSet("SELECT * FROM T_KESHI WHERE KS_DEL=0");
            foreach (DataRow row in ds.Tables[0].Rows)
            {
                Keshi k = new Keshi();
                k.KeshiID = row["ks_id"].ToString();
                k.KeshiName = row["ks_name"].ToString() ;
                k.KeshiCode = row["ks_code"].ToString();
                ks.Add(k);
            }
            return ks;
        }

        public static List<Yishi> GetYishiByKeshi(Keshi k)
        {
            List<Yishi> ys = new List<Yishi>();
            String sql = "SELECT * FROM T_YISHI WHERE YS_DEL=0";
            if (k != null)
                sql += " and ys_ksid='" + k.KeshiID + "'";

            DataSet ds = dal.GetDataSet(sql);
            foreach (DataRow row in ds.Tables[0].Rows)
            {
                Yishi y = new Yishi();
                y.YishiCode = row["ys_code"].ToString();
                y.YishiName = row["ys_name"].ToString();
                y.YishiID = row["ys_id"].ToString();
                y.YishiKeshiID = row["ys_ksid"].ToString();
                ys.Add(y);
            }
            return ys;
        }

        public static Yishi GetYishiByCode(string code)
        {
            List<Yishi> ys = new List<Yishi>();
            String sql = "SELECT * FROM T_YISHI WHERE YS_DEL=0 AND YS_CODE='" + code + "'";

            DataSet ds = dal.GetDataSet(sql);
            if (ds.Tables[0].Rows.Count == 0)
                throw new Exception("未找到编号是'" + code + "'的医师");

            DataRow row = ds.Tables[0].Rows[0];
            Yishi y = new Yishi();
            y.YishiCode = row["ys_code"].ToString();
            y.YishiName = row["ys_name"].ToString();
            y.YishiID = row["ys_id"].ToString();
            y.YishiKeshiID = row["ys_ksid"].ToString();
            
            return y;
        }

        public static void AddKeshi(Keshi keshi)
        {
            keshi.KeshiID = Guid.NewGuid().ToString();
            String sql = "INSERT INTO T_KESHI (ks_id, ks_code, ks_name, ks_del) VALUES('" + keshi.KeshiID + "','";
            sql += keshi.KeshiCode + "','" + keshi.KeshiName + "',0)";
            dal.ExecuteNonQuery(sql);
        }

        public static void ModifyKeshi(Keshi keshi)
        {
            String sql = "UPDATE T_KESHI SET ks_code='" + keshi.KeshiCode + "', ks_name='" + keshi.KeshiName + "' ";
            sql += " WHERE ks_id='" + keshi.KeshiID + "'";
            dal.ExecuteNonQuery(sql);
        }

        public static void DeleteKeshi(Keshi keshi)
        {
            String sql = "UPDATE T_KESHI SET ks_del=1 WHERE ks_id='" + keshi.KeshiID + "'";
            dal.ExecuteNonQuery(sql);
        }

        public static void AddYishi(Yishi yishi)
        {
            yishi.YishiID = Guid.NewGuid().ToString();
            String sql = "INSERT INTO T_YISHI (ys_id, ys_code, ys_name, ys_ksid, ys_del) VALUES('";
            sql += yishi.YishiID + "','" + yishi.YishiCode + "','" + yishi.YishiName + "','" + yishi.YishiKeshiID + "',0)";
            dal.ExecuteNonQuery(sql);
        }

        public static void ModifyYishi(Yishi yishi)
        {
            String sql = "UPDATE T_YISHI SET ys_code='" + yishi.YishiCode + "', ys_name='" + yishi.YishiName + "' ";
            sql += ", ys_ksid='" + yishi.YishiKeshiID + "' WHERE ys_id='" + yishi.YishiID + "'";
            dal.ExecuteNonQuery(sql);
        }

        public static void DeleteYishi(Yishi yishi)
        {
            String sql = "UPDATE T_YISHI SET ys_del=1 WHERE ys_id='" + yishi.YishiID + "'";
            dal.ExecuteNonQuery(sql);
        }

        public static List<Yaopin> GetAllYaopin()
        {
            List<Yaopin> ks = new List<Yaopin>();
            DataSet ds = dal.GetDataSet("SELECT * FROM T_YAOPIN WHERE yp_del=0 ORDER BY yp_code");
            foreach (DataRow row in ds.Tables[0].Rows)
            {
                Yaopin k = new Yaopin();
                k.YaopinID = row["yp_id"].ToString();
                k.YaopinName = row["yp_name"].ToString();
                k.YaopinCode = row["yp_code"].ToString();
                k.YaopinGuige = row["yp_guige"].ToString();
                k.YaopinDanwei = row["yp_danwei"].ToString();
                k.YaopinBatch = row["yp_pihao"].ToString();
                ks.Add(k);
            }
            return ks;
        }

        public static Yaopin GetYaopinByCode(string code)
        {
            Yaopin k = new Yaopin();
            if (code == null || code.Length == 0)
                return k;

            DataSet ds = dal.GetDataSet("SELECT * FROM T_YAOPIN WHERE yp_del=0 and yp_code='" + code + "'");
            if (ds.Tables[0].Rows.Count == 0)
                throw new Exception("未找到编号是'" + code + "'的药品");

            DataRow row = ds.Tables[0].Rows[0];
            k.YaopinID = row["yp_id"].ToString();
            k.YaopinName = row["yp_name"].ToString();
            k.YaopinCode = row["yp_code"].ToString();
            k.YaopinGuige = row["yp_guige"].ToString();
            k.YaopinDanwei = row["yp_danwei"].ToString();
            k.YaopinBatch = row["yp_pihao"].ToString();
            
            return k;
        }

        public static Yaopin GetYaopinByID(string id)
        {
            Yaopin k = new Yaopin();
            if (id == null || id.Length == 0)
                return k;

            DataSet ds = dal.GetDataSet("SELECT * FROM T_YAOPIN WHERE yp_del=0 and yp_id='" + id + "'");
            if (ds.Tables[0].Rows.Count == 0)
                throw new Exception("未找到编号是'" + id + "'的药品");

            DataRow row = ds.Tables[0].Rows[0];
            k.YaopinID = row["yp_id"].ToString();
            k.YaopinName = row["yp_name"].ToString();
            k.YaopinCode = row["yp_code"].ToString();
            k.YaopinGuige = row["yp_guige"].ToString();
            k.YaopinDanwei = row["yp_danwei"].ToString();
            k.YaopinBatch = row["yp_pihao"].ToString();

            return k;
        }

        public static void AddYaopin(Yaopin yaopin)
        {
            yaopin.YaopinID = Guid.NewGuid().ToString();
            String sql = "INSERT INTO T_YAOPIN (yp_id, yp_code, yp_name, yp_guige, yp_danwei, yp_pihao, yp_del) VALUES('";
            sql += yaopin.YaopinID + "','" + yaopin.YaopinCode + "','" + yaopin.YaopinName + "','" + 
                yaopin.YaopinGuige + "','" + yaopin.YaopinDanwei + "','" + yaopin.YaopinBatch + "',0)";
            dal.ExecuteNonQuery(sql);
        }

        public static void ModifyYaopin(Yaopin yaopin)
        {
            String sql = "UPDATE T_YAOPIN SET yp_code='" + yaopin.YaopinCode + "', yp_name='" + yaopin.YaopinName + "' ";
            sql += ", yp_guige='" + yaopin.YaopinGuige + "', yp_danwei='" + yaopin.YaopinDanwei + 
                "', yp_pihao='" + yaopin.YaopinBatch + "' WHERE yp_id='" + yaopin.YaopinID + "'";
            dal.ExecuteNonQuery(sql);
        }

        public static void UpdateYaopinBatch(Yaopin yaopin)
        {
            String sql = "UPDATE T_YAOPIN SET yp_pihao='" + yaopin.YaopinBatch + "' WHERE yp_id='" + yaopin.YaopinID + "'";
            dal.ExecuteNonQuery(sql);
        }

        public static void DeleteYaopin(Yaopin yaopin)
        {
            String sql = "UPDATE T_YAOPIN SET yp_del=1 WHERE yp_id='" + yaopin.YaopinID + "'";
            dal.ExecuteNonQuery(sql);
        }


        public static List<Lingyao> GetAllLingyao(DateTime startDate, DateTime endDate)
        {
            string s = " and ly_riqi>='" + startDate.ToString() + "' and ly_riqi<='" + endDate.ToString() + "'";
            s += " ORDER BY ly_riqi DESC";
			return GetAllLingyao(s);
        }

		public static List<Lingyao> GetAllLingyao(DateTime startDate)
        {
            string s = " and ly_riqi='" + startDate.ToString() + "'";
            s += " ORDER BY ly_riqi DESC";
			return GetAllLingyao(s);
        }

        public static List<Lingyao> GetAllLingyaoByYaopin(Yaopin yaopin)
        {
            string s = " and ly_yaopinid='" + yaopin.YaopinID + "'";
            s += " ORDER BY ly_riqi DESC";
            return GetAllLingyao(s);
        }

		public static List<Lingyao> GetAllLingyao()
        {
            return GetAllLingyao("  ORDER BY ly_riqi DESC");
        }


		private static List<Lingyao> GetAllLingyao(String sqlCondition)
		{
			String sql = "SELECT * FROM T_LINGYAO l INNER JOIN T_YAOPIN y ON l.ly_yaopinid=y.yp_id WHERE ly_shuliang>0 ";
			if (sqlCondition != null)
			{
				sql += sqlCondition;
			}

			List<Lingyao> ys = new List<Lingyao>();
			DataSet ds = dal.GetDataSet(sql);
            foreach (DataRow row in ds.Tables[0].Rows)
            {
                Lingyao y = new Lingyao();
                y.LingyaoID = row["ly_id"].ToString();
                y.LingyaoDate = DateTime.Parse(row["ly_riqi"].ToString());
                y.LingyaoNumber = Int32.Parse(row["ly_shuliang"].ToString());
                y.LingyaoCode = row["ly_danjuhao"].ToString();
                y.LingyaoComment = row["ly_zhaiyao"].ToString();
                y.LinyaoYaopinID = row["ly_yaopinid"].ToString();
                
                Yaopin k = new Yaopin();
                k.YaopinID = row["yp_id"].ToString();
                k.YaopinName = row["yp_name"].ToString();
                k.YaopinCode = row["yp_code"].ToString();
                k.YaopinGuige = row["yp_guige"].ToString();
                k.YaopinDanwei = row["yp_danwei"].ToString();
                y.LingyaoYaopin = k;

                ys.Add(y);
            }
            return ys;
		}

        public static void AddLingyaon(Lingyao lingyao)
        {
            lingyao.LingyaoID = Guid.NewGuid().ToString();
            String sql = "INSERT INTO T_LINGYAO (ly_id, ly_yaopinid, ly_riqi, ly_shuliang, ly_danjuhao, ly_zhaiyao) VALUES('";
            sql += lingyao.LingyaoID + "','" + lingyao.LinyaoYaopinID + "','" + lingyao.LingyaoDate + "'," + 
                lingyao.LingyaoNumber + ",'" + lingyao.LingyaoCode + "','" + lingyao.LingyaoComment +"')";
            dal.ExecuteNonQuery(sql);
        }

        public static void ModifyLingyao(Lingyao lingyao)
        {
            String sql = "UPDATE T_LINGYAO SET ly_yaopinid='" + lingyao.LinyaoYaopinID + "', ly_riqi='" + lingyao.LingyaoDate + "' ";
            sql += ", ly_shuliang=" + lingyao.LingyaoNumber + ", ly_danjuhao='" + lingyao.LingyaoCode + "', ly_zhaiyao='" + lingyao.LingyaoComment;
            sql += "' WHERE ly_id='" + lingyao.LingyaoID + "'";
            dal.ExecuteNonQuery(sql);
        }

        public static void DeleteLingyao(Lingyao lingyao)
        {
            String sql = "DELETE FROM T_LINGYAO WHERE ly_id='" + lingyao.LingyaoID + "'";
            dal.ExecuteNonQuery(sql);
        }

        public static List<Chufang> GetAllChufangList()
        {
            return GetChufangList("ORDER BY ly_riqi DESC");
        }

        public static List<Chufang> GetAllZhuyuanChufangList(string yaopinID, DateTime startDate, DateTime endDate,
            bool sortByDrug, bool sortByDoctor, bool SortByPatient)
        {
            string condition = " AND cf_iszhuyuan=1 ";
            if (!String.IsNullOrEmpty(yaopinID))
                condition += " AND yp_id='" + yaopinID + "'";
            condition += " AND ly_riqi>=#" + startDate.ToShortDateString() + "# AND ly_riqi<=#" + endDate.ToShortDateString() + "# ";
            string filter = "";
            if (sortByDrug)
                filter += " ORDER BY yp_code ASC";
            if (sortByDoctor)
                if (filter.Length > 0)
                    filter += ", yk.ks_id ASC, ys_code ASC";
                else
                    filter += "ORDER BY yk.ks_id ASC, ys_code ASC";
            if (SortByPatient)
                if (filter.Length > 0)
                    filter += ", bk.ks_id ASC, cf_hzxingming ASC";
                else
                    filter += "ORDER BY bk.ks_id ASC, cf_hzxingming ASC";
            if (filter.Length > 0)
                filter += ", ly_riqi,  cf_serial ASC";
            else
                filter += " ORDER BY ly_riqi, cf_serial ASC";
            return GetChufangList(condition + filter);
        }

        public static List<Chufang> GetAllMenzhenChufangList(string yaopinID, DateTime startDate, DateTime endDate,
            bool sortByDrug, bool sortByDoctor, bool SortByPatient)
        {
            string condition = " AND cf_iszhuyuan=0 ";
            if (!String.IsNullOrEmpty(yaopinID))
                condition += " AND yp_id='" + yaopinID + "'";
            condition += " AND ly_riqi>=#" + startDate.ToShortDateString() + "# AND ly_riqi<=#" + endDate.ToShortDateString() + "# ";
            string filter = "";
            if (sortByDrug)
                filter += " ORDER BY yp_code ASC";
            if (sortByDoctor)
                if (filter.Length > 0)
                    filter += ", yk.ks_id ASC, ys_code ASC";
                else
                    filter += "ORDER BY yk.ks_id ASC, ys_code ASC";
            if (SortByPatient)
                if (filter.Length > 0)
                    filter += ", bk.ks_id ASC, cf_hzxingming ASC";
                else
                    filter += "ORDER BY bk.ks_id ASC, cf_hzxingming ASC";
            if (filter.Length > 0)
                filter += ", ly_riqi,  cf_serial ASC";
            else
                filter += " ORDER BY ly_riqi, cf_serial ASC";
            return GetChufangList(condition + filter);
        }

        public static List<Chufang> GetAllChufangList(string yaopinID, DateTime startDate, DateTime endDate, 
            bool sortByDrug, bool sortByDoctor, bool SortByPatient)
        {
            string condition = "";
            if (!String.IsNullOrEmpty(yaopinID))
                condition += " AND yp_id='" + yaopinID + "'";
            condition += " AND ly_riqi>=#" + startDate.ToShortDateString() + "# AND ly_riqi<=#" + endDate.ToShortDateString() + "# ";
            string filter = "";
            if (sortByDrug)
                filter += " ORDER BY yp_code ASC";
            if (sortByDoctor)
                if (filter.Length > 0)
                    filter += ", yk.ks_id ASC, ys_code ASC";
                else
                    filter += "ORDER BY yk.ks_id ASC, ys_code ASC";
            if (SortByPatient)
                if (filter.Length > 0)
                    filter += ", bk.ks_id ASC, cf_hzxingming ASC";
                else
                    filter += "ORDER BY bk.ks_id ASC, cf_hzxingming ASC";
            if (filter.Length > 0)
                filter += ", ly_riqi,  cf_serial ASC";
            else
                filter += " ORDER BY ly_riqi, cf_serial ASC";
            return GetChufangList(condition + filter);
        }

        public static List<Chufang> GetChufangList(string sqlCondition)
        {
            String sql = "SELECT l.*, c.*, y.*, s.*, bk.ks_id AS bk_ks_id, bk.ks_name AS bk_ks_name, bk.ks_code AS bk_ks_code, " +
                " yk.ks_id AS yk_ks_id, yk.ks_name AS yk_ks_name, yk.ks_code AS yk_ks_code " +
                " FROM T_LINGYAO l,T_CHUFANG c, T_YAOPIN y, T_YISHI s, T_KESHI bk, T_KESHI yk  " + 
                " WHERE c.cf_id=l.ly_id AND l.ly_yaopinid=y.yp_id AND c.cf_yishi=s.ys_id AND " +
                " c.cf_hzkeshi=bk.ks_id AND c.cf_yskeshi=yk.ks_id ";
            if (sqlCondition != null && sqlCondition.Length > 0)
            {
                sql += sqlCondition;
            }

            List<Chufang> cf = new List<Chufang>();
            DataSet ds = dal.GetDataSet(sql);
            foreach (DataRow row in ds.Tables[0].Rows)
            {
                Chufang c = new Chufang();
                Lingyao y = c.Lingyao;
                y.LingyaoID = row["ly_id"].ToString();
                y.LingyaoDate = DateTime.Parse(row["ly_riqi"].ToString());
                y.LingyaoNumber = Int32.Parse(row["ly_shuliang"].ToString());
                y.LingyaoCode = row["ly_danjuhao"].ToString();
                y.LingyaoComment = row["ly_zhaiyao"].ToString();
                y.LinyaoYaopinID = row["ly_yaopinid"].ToString();

                Yaopin k = new Yaopin();
                k.YaopinID = row["yp_id"].ToString();
                k.YaopinName = row["yp_name"].ToString();
                k.YaopinCode = row["yp_code"].ToString();
                k.YaopinGuige = row["yp_guige"].ToString();
                k.YaopinDanwei = row["yp_danwei"].ToString();
                y.LingyaoYaopin = k;

                Yishi s = c.Doctor;
                s.YishiCode = row["ys_code"].ToString();
                s.YishiName = row["ys_name"].ToString();
                s.YishiID = row["ys_id"].ToString();
                s.YishiKeshiID = row["ys_ksid"].ToString();

                Keshi bk = c.PatientDept;
                bk.KeshiID = row["bk_ks_id"].ToString();
                bk.KeshiName = row["bk_ks_name"].ToString();
                bk.KeshiCode = row["bk_ks_code"].ToString();

                Keshi yk = c.DoctorDept;
                yk.KeshiID = row["yk_ks_id"].ToString();
                yk.KeshiName = row["yk_ks_name"].ToString();
                yk.KeshiCode = row["yk_ks_code"].ToString();

                c.PatientName = row["cf_hzxingming"].ToString();
                c.PatientSex = Int32.Parse(row["cf_hzxingbie"].ToString());
                c.PatientAge = Int32.Parse(row["cf_hznianling"].ToString());
                c.PatientCardNo = row["cf_hzshenfenzheng"].ToString();
                c.HospitalNo = row["cf_zhuyuanhao"].ToString();
                c.Illness = row["cf_jibing"].ToString();
                c.TakerName = row["cf_qyxingming"].ToString();
                c.TakerCardNo = row["cf_qyshenfenzheng"].ToString();
                c.ReleaserName = row["cf_fyxingming"].ToString();
                c.AuditorName = row["cf_fhxingming"].ToString();
                c.BatchNo = row["cf_pihao"].ToString();
                c.IsHospital = Int32.Parse(row["cf_iszhuyuan"].ToString());
                c.IsCancer = Int32.Parse(row["cf_isaizheng"].ToString());
                c.Serial = Int32.Parse(row["cf_serial"].ToString());
                cf.Add(c);
            }
            return cf;
        }

        public static String AddChufang(Chufang chufang)
        {
            chufang.Lingyao.LingyaoID = Guid.NewGuid().ToString();
            Lingyao lingyao = chufang.Lingyao;
            String sql = "INSERT INTO T_LINGYAO (ly_id, ly_yaopinid, ly_riqi, ly_shuliang, ly_danjuhao, ly_zhaiyao) VALUES('";
            sql += lingyao.LingyaoID + "','" + lingyao.LinyaoYaopinID + "','" + lingyao.LingyaoDate + "'," +
                lingyao.LingyaoNumber + ",'" + lingyao.LingyaoCode + "','')";
            dal.ExecuteNonQuery(sql, true, false);

            sql = "INSERT INTO T_CHUFANG (cf_id, cf_hzxingming, cf_hzxingbie, cf_hznianling, cf_hzshenfenzheng, cf_zhuyuanhao, ";
            sql += " cf_jibing, cf_yishi, cf_qyxingming, cf_qyshenfenzheng, cf_fyxingming, cf_fhxingming, ";
            sql += "cf_pihao, cf_hzkeshi, cf_yskeshi, cf_iszhuyuan, cf_isaizheng, cf_serial) VALUES('";
            sql += lingyao.LingyaoID + "','" + chufang.PatientName + "'," + chufang.PatientSex + "," + chufang.PatientAge + ", '" +
                chufang.PatientCardNo + "','" + chufang.HospitalNo + "','" + chufang.Illness + "','" + chufang.Doctor.YishiID + "','" +
                chufang.TakerName + "','" + chufang.TakerCardNo + "','" + chufang.ReleaserName + "','" + chufang.AuditorName + "','" +
                chufang.BatchNo + "','" + chufang.PatientDept.KeshiID + "','" + chufang.DoctorDept.KeshiID + "'," + 
                chufang.IsHospital + "," + chufang.IsCancer + "," + chufang.Serial + ")";
            dal.ExecuteNonQuery(sql, false, true);

            try
            {
                UpdatePersons(chufang);
                if (!String.IsNullOrEmpty(chufang.BatchNo))
                {
                    Yaopin y = new Yaopin();
                    y.YaopinID = chufang.Lingyao.LinyaoYaopinID;
                    y.YaopinBatch = chufang.BatchNo;
                    UpdateYaopinBatch(y);
                }
            }
            catch (Exception e)
            {
            }
            return chufang.Lingyao.LingyaoID;
        }

        public static void ModifyChufang(Chufang chufang)
        {
            Lingyao lingyao = chufang.Lingyao;
            String sql = "UPDATE T_LINGYAO SET ly_yaopinid='" + lingyao.LinyaoYaopinID + "', ly_riqi='" + lingyao.LingyaoDate + "' ";
            sql += ", ly_shuliang=" + lingyao.LingyaoNumber + ", ly_danjuhao='" + lingyao.LingyaoCode;
            sql += "' WHERE ly_id='" + lingyao.LingyaoID + "'";
            dal.ExecuteNonQuery(sql, true, false);

            sql = "UPDATE T_CHUFANG SET cf_hzxingming='" + chufang.PatientName + "', cf_hzxingbie=" + chufang.PatientSex + ", ";
            sql += "cf_hznianling=" + chufang.PatientAge + ", cf_hzshenfenzheng='" + chufang.PatientCardNo;
            sql += "', cf_zhuyuanhao='" + chufang.HospitalNo + "' , cf_jibing='" + chufang.Illness + "' , cf_yishi='" + chufang.Doctor.YishiID;
            sql += "', cf_qyxingming='" + chufang.TakerName + "', cf_qyshenfenzheng='" + chufang.TakerCardNo;
            sql += "', cf_fyxingming='" + chufang.ReleaserName + "', cf_fhxingming='" + chufang.AuditorName;
            sql += "', cf_pihao='" + chufang.BatchNo + "', cf_hzkeshi='" + chufang.PatientDept.KeshiID + "', cf_yskeshi='" + chufang.DoctorDept.KeshiID;
            sql += "', cf_iszhuyuan=" + chufang.IsHospital + ", cf_isaizheng=" + chufang.IsCancer + ", cf_serial=" + chufang.Serial;
            sql += " WHERE cf_id='" + lingyao.LingyaoID + "'";
            dal.ExecuteNonQuery(sql, false, true);

            try
            {
                UpdatePersons(chufang);
                if (!String.IsNullOrEmpty(chufang.BatchNo))
                {
                    Yaopin y = new Yaopin();
                    y.YaopinID = chufang.Lingyao.LinyaoYaopinID;
                    y.YaopinBatch = chufang.BatchNo;
                    UpdateYaopinBatch(y);
                }
            }
            catch (Exception e)
            {
            }
        }

        public static void DeleteChufang(Chufang chufang)
        {
            Lingyao lingyao = chufang.Lingyao;
            String sql = "DELETE FROM T_LINGYAO WHERE ly_id='" + lingyao.LingyaoID + "'";
            dal.ExecuteNonQuery(sql, true, false);

            sql = "DELETE FROM T_CHUFANG WHERE cf_id='" + lingyao.LingyaoID + "'";
            dal.ExecuteNonQuery(sql, false, true);
        }

        public static int GetChufangIndex(int isHospital, DateTime startDate)
        {
            DateTime baseDate = startDate;
            if (startDate.Day >= 26)
                baseDate = new DateTime(startDate.Year, startDate.Month, 26);
            else if (startDate.Day >= 16)
                baseDate = new DateTime(startDate.Year, startDate.Month, 16);
            else if (startDate.Day >= 6)
                baseDate = new DateTime(startDate.Year, startDate.Month, 6);
            else
            {
                baseDate.AddMonths(-1);
                baseDate = new DateTime(baseDate.Year, baseDate.Month, 26);
            }

            DateTime prevDate = startDate.AddDays(-1);

            String sql = "SELECT COUNT(*) FROM T_CHUFANG c, T_LINGYAO l WHERE c.cf_id=l.ly_id AND cf_iszhuyuan=" + isHospital;
            sql += " AND ly_riqi>=#" + baseDate.ToShortDateString() + "# AND ly_riqi<=#" + prevDate.ToShortDateString() + "# ";
            return (int)dal.GetDataScalar(sql);
        }

        private static List<YaopinSum> GetDrugStore(DateTime beforeDate)
        {
            String sql = "SELECT y.yp_id, y.yp_code, y.yp_name, y.yp_guige, y.yp_danwei, SUM(l.ly_shuliang) AS yp_kucun" +
                " FROM T_YAOPIN y LEFT JOIN T_LINGYAO l ON y.yp_id=l.ly_yaopinid " +
                " WHERE y.yp_del=0 AND l.ly_riqi<#" + beforeDate.ToShortDateString() + "# " +
                " GROUP BY y.yp_id, y.yp_code, y.yp_name, y.yp_guige, y.yp_danwei ORDER BY y.yp_code ASC";

            DataTable dt = dal.GetDataTable(sql);

            List<YaopinSum> yaopinSum = new List<YaopinSum>();
            foreach (DataRow row in dt.Rows)
            {
                YaopinSum ys = new YaopinSum();
                ys.Yaopin.YaopinID = row["yp_id"].ToString();
                ys.Yaopin.YaopinName = row["yp_name"].ToString();
                ys.Yaopin.YaopinGuige = row["yp_guige"].ToString();
                ys.Yaopin.YaopinDanwei = row["yp_danwei"].ToString();
                ys.Yaopin.YaopinCode = row["yp_code"].ToString();
                ys.Sum = Int32.Parse(row["yp_kucun"].ToString());
                yaopinSum.Add(ys);
            }

            return yaopinSum;
        }

        private static List<YaopinSum> GetDrugUsage(DateTime startDate, DateTime endDate, int isHospital)
        {
            string sql = "SELECT SUM(l.ly_shuliang) AS zongshu, l.ly_yaopinid" +
                " FROM T_CHUFANG c LEFT JOIN T_LINGYAO l ON c.cf_id=l.ly_id  " +
                " WHERE l.ly_riqi>=#" + startDate.ToShortDateString() + "# AND ly_riqi<=#"+ endDate.ToShortDateString() + "# ";
            if (isHospital == 1)
                sql += " AND c.cf_iszhuyuan=1 ";
            else if (isHospital == 0)
                sql += " AND c.cf_iszhuyuan=0 ";
            sql += "GROUP BY l.ly_yaopinid ";

            DataTable dt = dal.GetDataTable(sql);

            List<YaopinSum> yaopinSum = new List<YaopinSum>();
            
            foreach (DataRow row in dt.Rows)
            {
                YaopinSum ys = new YaopinSum();
                ys.Yaopin.YaopinID = row["ly_yaopinid"].ToString();
                ys.Sum = -Int32.Parse(row["zongshu"].ToString());
                yaopinSum.Add(ys);    
            }

            return yaopinSum;
        }

        private static List<YaopinSum> GetDrugPurchase(DateTime startDate, DateTime endDate)
        {
            string sql = "SELECT SUM(ly_shuliang) AS zongshu, ly_yaopinid" +
                " FROM T_LINGYAO " +
                " WHERE ly_shuliang > 0 AND ly_riqi>=#" + startDate.ToShortDateString() + "# AND ly_riqi<=#" + endDate.ToShortDateString() + "# ";
            sql += "GROUP BY ly_yaopinid ";
            DataTable dt = dal.GetDataTable(sql);
            List<YaopinSum> yaopinSum = new List<YaopinSum>();

            foreach (DataRow row in dt.Rows)
            {
                YaopinSum ys = new YaopinSum();
                ys.Yaopin.YaopinID = row["ly_yaopinid"].ToString();
                ys.Sum = Int32.Parse(row["zongshu"].ToString());
                yaopinSum.Add(ys);
            }

            return yaopinSum;
        }

        public static List<YaopinAccount> GetSummary(DateTime start, DateTime end)
        {
            DateTime next = end;
            next = next.AddDays(1);

            List<Yaopin> yaopins = GetAllYaopin();
            List<YaopinSum> sumCurrent = GetDrugStore(next);
            List<YaopinSum> sumBefore = GetDrugStore(start);
            List<YaopinSum> sumPurchase = GetDrugPurchase(start, end);
            List<YaopinSum> sumHospital = GetDrugUsage(start, end, 1);
            List<YaopinSum> sumOutpatient = GetDrugUsage(start, end, 0);
            List<YaopinSum> sumTotal = GetDrugUsage(start, end, 2);

            List<YaopinAccount> yas = new List<YaopinAccount>();

            foreach (Yaopin yp in yaopins)
            {
                YaopinAccount ya = new YaopinAccount();
                ya.Yaopin = yp;
                

                foreach (YaopinSum ys in sumCurrent)
                {
                    if (ys.Yaopin.YaopinID == ya.Yaopin.YaopinID)
                    {
                        ya.SumCurrent = ys.Sum;
                        break;
                    }
                }

                foreach (YaopinSum y1 in sumBefore)
                {
                    if (y1.Yaopin.YaopinID == ya.Yaopin.YaopinID)
                    {
                        ya.SumBefore = y1.Sum;
                        break;
                    }
                }

                foreach (YaopinSum y2 in sumPurchase)
                {
                    if (y2.Yaopin.YaopinID == ya.Yaopin.YaopinID)
                    {
                        ya.SumPurchase = y2.Sum;
                        break;
                    }
                }

                foreach (YaopinSum y3 in sumHospital)
                {
                    if (y3.Yaopin.YaopinID == ya.Yaopin.YaopinID)
                    {
                        ya.SumUsageHospital = y3.Sum;
                        break;
                    }
                }

                foreach (YaopinSum y4 in sumOutpatient)
                {
                    if (y4.Yaopin.YaopinID == ya.Yaopin.YaopinID)
                    {
                        ya.SumUsageOutpatient = y4.Sum;
                        break;
                    }
                }

                foreach (YaopinSum y5 in sumTotal)
                {
                    if (y5.Yaopin.YaopinID == ya.Yaopin.YaopinID)
                    {
                        ya.SumUsageTotal = y5.Sum;
                        break;
                    }
                }

                yas.Add(ya);
            }
            return yas;
        }

        public static List<YaopinAccount> GetMonthSummary(int year, int month)
        {
            DateTime end = new DateTime(year, month, 25);
            DateTime start = end;
            start = start.AddMonths(-1);
            start = new DateTime(start.Year, start.Month, 26);
            return GetSummary(start, end);
        }

        public static List<YaopinAccount> GetDayAccount(DateTime date)
        {
            DateTime end = date;
            DateTime start = end;
            List<YaopinAccount> ya = GetSummary(start, end);
            for (int i = 0; i < ya.Count; i++)
            {
                ya[i].AccountDate = date;
            }
            return ya;
        }

        public static List<YaopinAccount> GetDrugAccount(String yaopinID, DateTime startDate, DateTime endDate)
        {
            String sql = "SELECT y.yp_id, y.yp_code, y.yp_name, y.yp_guige, y.yp_danwei, SUM(l.ly_shuliang) AS yp_kucun" +
                " FROM T_YAOPIN y LEFT JOIN T_LINGYAO l ON y.yp_id=l.ly_yaopinid " +
                " WHERE y.yp_id='" + yaopinID + "' AND l.ly_riqi<#" + startDate.ToShortDateString() + "# " +
                " GROUP BY y.yp_id, y.yp_code, y.yp_name, y.yp_guige, y.yp_danwei ORDER BY y.yp_code ASC";

            DataTable dt = dal.GetDataTable(sql);
            YaopinSum ys = new YaopinSum();
            if (dt.Rows.Count == 0)
            {
                Yaopin y = GetYaopinByID(yaopinID);
                if (y == null)
                    return new List<YaopinAccount>();
                else
                {
                    ys.Yaopin = y;
                }
            }
            else
            {
                DataRow row = dt.Rows[0];
                ys.Yaopin.YaopinID = row["yp_id"].ToString();
                ys.Yaopin.YaopinName = row["yp_name"].ToString();
                ys.Yaopin.YaopinGuige = row["yp_guige"].ToString();
                ys.Yaopin.YaopinDanwei = row["yp_danwei"].ToString();
                ys.Yaopin.YaopinCode = row["yp_code"].ToString();
                ys.Sum = Int32.Parse(row["yp_kucun"].ToString());
            }

            sql = "SELECT * FROM T_LINGYAO  WHERE ly_yaopinid='" + yaopinID +
                "' AND ly_riqi>=#" + startDate.ToShortDateString() + "# AND ly_riqi<=#" + endDate.ToShortDateString() + "# " +
                " ORDER BY ly_riqi ASC";
            dt = dal.GetDataTable(sql);

            List<YaopinAccount> yas = new List<YaopinAccount>();
            YaopinAccount ya = null;

            foreach (DataRow r in dt.Rows)
            {
                DateTime date = DateTime.Parse(r["ly_riqi"].ToString());
                if (ya != null && ya.AccountDate != date)
                {
                    ya.SumUsageTotal = -ya.SumUsageTotal;
                    yas.Add(ya);
                    ya = new YaopinAccount();
                }
                if (ya == null)
                    ya = new YaopinAccount();

                ya.AccountDate = date;
                ya.Yaopin = ys.Yaopin;
                int sum = Int32.Parse(r["ly_shuliang"].ToString());
                if (sum > 0)
                    ya.SumPurchase += sum;
                else
                    ya.SumUsageTotal += sum;
                ys.Sum += sum;
                ya.SumCurrent = ys.Sum;
            }

            if (ya != null)
            {
                ya.SumUsageTotal = -ya.SumUsageTotal;
                yas.Add(ya);
            }

            return yas;
        }

        public static List<Person> GetPersons(int personType)
        {
            string sql = "SELECT * FROM T_RENYUAN WHERE ry_type=" + personType + " ORDER BY ry_lastuse DESC";
            DataTable dt = dal.GetDataTable(sql);
            List<Person> persons = new List<Person>();

            foreach (DataRow row in dt.Rows)
            {
                Person p = new Person();
                p.PersonName = row["ry_name"].ToString();
                p.PersonCardNo = row["ry_cardno"].ToString();
                p.PersonType = Int32.Parse(row["ry_type"].ToString());
                p.PersonPYM = row["ry_pym"].ToString();
                p.IsZhuyuan = Convert.ToInt32(row["ry_zhuyuan"].ToString());
                p.KeshiID = row["ry_dept"].ToString();
                persons.Add(p);
            }

            return persons;
        }

        public static void SavePerson(Person person)
        {
            if (String.IsNullOrEmpty(person.PersonName))
                return;

            string sql = "SELECT * FROM T_RENYUAN WHERE ry_type=" + person.PersonType + 
                "AND ry_name='" + person.PersonName + "'";
            DataTable dt = dal.GetDataTable(sql);
            if (dt.Rows.Count == 0)
            {
                sql = "INSERT INTO T_RENYUAN (ry_name, ry_cardno, ry_type, ry_pym, ry_lastuse, ry_zhuyuan, ry_dept) VALUES " +
                    "('" + person.PersonName + "', '" + person.PersonCardNo + "'," +
                    person.PersonType + ",'" + person.PersonPYM + "','" + person.PersonLastUse.ToString() + "', " + 
                    person.IsZhuyuan + ", '" + person.KeshiID + "')";
            }
            else
            {
                sql = "UPDATE T_RENYUAN SET ry_cardno='" + person.PersonCardNo + "', ry_lastuse='"
                    + person.PersonLastUse.ToString() + "', ry_zhuyuan=" + person.IsZhuyuan + ", ry_dept='" +
                    person.KeshiID + "' WHERE ry_type=" + person.PersonType +
                    "AND ry_name='" + person.PersonName + "'"; 
            }

            dal.ExecuteNonQuery(sql);
        }

        private static void UpdatePersons(Chufang chufang)
        {
            Person p = new Person();
            p.PersonLastUse = DateTime.Now;
            p.PersonName = chufang.TakerName;
            p.PersonType = Person.PERSON_TAKER;
            p.IsZhuyuan = chufang.IsHospital;
            p.KeshiID = chufang.DoctorDeptID;

            SavePerson(p);

            p.PersonName = chufang.ReleaserName;
            p.PersonType = Person.PERSON_SENDER;
            SavePerson(p);


            p.PersonName = chufang.AuditorName;
            p.PersonType = Person.PERSON_AUDITOR;
            SavePerson(p);
        }
    }
}
